﻿using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Threading.Tasks;
using WalkingTec.Mvvm.Core;
using WalkingTec.Mvvm.Core.Extensions;
using SigMIS.Model;
using NPOI.SS.Formula.Functions;
using System.Data.Odbc;
using System.Data;

namespace SigMIS.ViewModel.Equipment.RoadLineVMs
{
    public partial class RoadLineTemplateVM : BaseTemplateVM
    {
        [Display(Name = "信息点代码")]
        public ExcelPropety POICode_Excel = ExcelPropety.CreateProperty<RoadLine>(x => x.POICode);
        [Display(Name = "信息点名称")]
        public ExcelPropety POIName_Excel = ExcelPropety.CreateProperty<RoadLine>(x => x.POIName);
        [Display(Name = "信息点属性")]
        public ExcelPropety POIAttribute_Excel = ExcelPropety.CreateProperty<RoadLine>(x => x.POIAttribute);
        [Display(Name = "工作模式")]
        public ExcelPropety WorkMode_Excel = ExcelPropety.CreateProperty<RoadLine>(x => x.WorkMode);
        [Display(Name = "经度")]
        public ExcelPropety Longitude_Excel = ExcelPropety.CreateProperty<RoadLine>(x => x.Longitude);
        [Display(Name = "纬度")]
        public ExcelPropety Latitude_Excel = ExcelPropety.CreateProperty<RoadLine>(x => x.Latitude);
        [Display(Name = "公里标")]
        public ExcelPropety Mileage_Excel = ExcelPropety.CreateProperty<RoadLine>(x => x.Mileage);
        [Display(Name = "所属路局名称")]
        public ExcelPropety AtBureauName_Excel = ExcelPropety.CreateProperty<RoadLine>(x => x.AtBureauName);
        [Display(Name = "所属路局代码")]
        public ExcelPropety AtBureauCode_Excel = ExcelPropety.CreateProperty<RoadLine>(x => x.AtBureauCode);
        [Display(Name = "所属线路名称")]
        public ExcelPropety AtRoadLineName_Excel = ExcelPropety.CreateProperty<RoadLine>(x => x.AtRoadLineName);
        [Display(Name = "所属线路代码")]
        public ExcelPropety AtRoadLineCode_Excel = ExcelPropety.CreateProperty<RoadLine>(x => x.AtRoadLineCode);
        [Display(Name = "所属区段名称")]
        public ExcelPropety AtSectionName_Excel = ExcelPropety.CreateProperty<RoadLine>(x => x.AtSectionName);
        [Display(Name = "所属调度区段名称")]
        public ExcelPropety AtDispatchingName_Excel = ExcelPropety.CreateProperty<RoadLine>(x => x.AtDispatchingName);
        [Display(Name = "车站名称")]
        public ExcelPropety StationName_Excel = ExcelPropety.CreateProperty<RoadLine>(x => x.StationName);
        [Display(Name = "车站代码")]
        public ExcelPropety StationCode_Excel = ExcelPropety.CreateProperty<RoadLine>(x => x.StationCode);
        [Display(Name = "车站电话号码")]
        public ExcelPropety StationPhone_Excel = ExcelPropety.CreateProperty<RoadLine>(x => x.StationPhone);
        [Display(Name = "曲径")]
        public ExcelPropety Meandering_Excel = ExcelPropety.CreateProperty<RoadLine>(x => x.Meandering);
        [Display(Name = "海拔")]
        public ExcelPropety Altitude_Excel = ExcelPropety.CreateProperty<RoadLine>(x => x.Altitude);
        [Display(Name = "归属GRIS的IP地址")]
        public ExcelPropety IP_Excel = ExcelPropety.CreateProperty<RoadLine>(x => x.IP);

	    protected override void InitVM()
        {
        }

    }

    public class RoadLineImportVM : BaseImportVM<RoadLineTemplateVM, RoadLine>
    {
        //当前导入数据的线路名
        [Required(ErrorMessage = "{0}是必填项")]
        public string RoadLineName { get; set; }

        protected override void InitVM()
        {
            base.InitVM();
        }

        public override bool BatchSaveData()
        {
            return base.BatchSaveData();
        }

        public override void SetTemplateData()
        {
            //覆盖模板数据，使用SetEntityData方法生成待导入数据
        }

        public override void SetEntityData()
        {
            //从MDB导入数据
            //重写MDB文件导入方法。
            //导入已上传的access线路表数据库文件的内容到线路表
            try
            {
                //【CHECK】上传附件的ID为空
                if (UploadFileId == null)
                {
                    ErrorListVM.EntityList.Add(new ErrorMessage { Message = Localizer["PleaseUploadTemplate"] });
                    return ;
                }

                //【CHECK】数据库中不存在附件ID对应的数据信息
                FileAttachment UploadFileInfo = DC.Set<FileAttachment>().Where(x => x.ID.ToString() == UploadFileId).FirstOrDefault();
                if (UploadFileInfo == null)
                {
                    ErrorListVM.EntityList.Add(new ErrorMessage { Message = Localizer["WrongTemplate"] });
                    return ;
                }

                //【CHECK】检查是否为Access数据库（*.mdb)
                if (UploadFileInfo.FileExt.ToLower().IndexOf("mdb") < 0)
                {
                    ErrorListVM.EntityList.Add(new ErrorMessage { Message = Localizer["PleaseUploadTemplate"] });
                    return ;
                }

                //打开MDB数据，并将数据导入RoadLines表
                string filePathName = UploadFileInfo.Path;

                if (!ImportMDB(filePathName))
                {
                    ErrorListVM.EntityList.Add(new ErrorMessage { Message = "打开MDB数据库失败！" });
                    return ;
                }
       
            }
            catch
            {
                ErrorListVM.EntityList.Add(new ErrorMessage { Message = Localizer["WrongTemplate"] });
            }
        }

        #region 导入MDB数据库
        bool ImportMDB(string filePathName)
        {
            //通过odbc访问access数据库，如果系统安装的是64位的MDAC驱动 则将程序编译x64运行，否则会出现： ERROR [IM002] [Microsoft][ODBC 驱动程序管理器] 未发现数据源名称并且未指定默认驱动程序

            string tableName = "export";    //MDB数据库表

            string connStr = @"Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + filePathName;
            
            var dt = GetDataTable(connStr, tableName);            
            if (dt == null) return false;


            //如果为SqlServer则使用bulk添加方式
            //var cs = this.ConfigInfo.Connections.FirstOrDefault();
            //if(cs.DbType == DBTypeEnum.SqlServer)
            //{
            //    //如果为sqlserver开启bulk添加模式提高效率
            //}
            //else
            //{
            //    //foreach (DataRow item in dt.Rows)
            //    //{
            //    //    DC.Set<RoadLine>().Add(item);                                               
            //    //}
            //}

            //添加数据到EntityList
            EntityList = new List<RoadLine>();

            try
            {
                foreach (DataRow item in dt.Rows)
                {
                    if (!double.TryParse(item[5].ToString().Trim(), out double longitude)) longitude = 0;
                    if (!double.TryParse(item[6].ToString().Trim(), out double latitude)) latitude = 0;
                    if (!uint.TryParse(item[7].ToString().Trim(), out uint mileage)) mileage = 0;
                    if (!double.TryParse(item[18].ToString().Trim(), out double altitude)) altitude = 0;

                    EntityList.Add(new RoadLine()
                    {
                        RoadLineName = RoadLineName,
                        POICode = item[1].ToString().Trim(),
                        POIName = item[2].ToString().Trim(),
                        POIAttribute = item[3].ToString().Trim(),
                        WorkMode = item[4].ToString().Trim(),
                        Longitude = longitude,  //item[5].ToString().Trim(),
                        Latitude = latitude,    //item[6].ToString().Trim(),
                        Mileage = mileage,      //item[7].ToString().Trim(),
                        AtBureauName = item[8].ToString().Trim(),
                        AtBureauCode = item[9].ToString().Trim(),
                        AtRoadLineName = item[10].ToString().Trim(),
                        AtRoadLineCode = item[11].ToString().Trim(),
                        AtSectionName = item[12].ToString().Trim(),
                        AtDispatchingName = item[13].ToString().Trim(),
                        StationName = item[14].ToString().Trim(),
                        StationCode = item[15].ToString().Trim(),
                        StationPhone = item[16].ToString().Trim(),
                        Meandering = item[17].ToString().Trim(),
                        Altitude = altitude,    //item[18].ToString().Trim()
                        IP = item[19].ToString().Trim(),
                    });
                }
            }
            catch(Exception)
            {
            }

            return true;
        }

        public static bool IsTableExist(string connStr, string tableName)
        {
            using(OdbcConnection conn = new OdbcConnection(connStr))
            try
            {
                conn.Open();
                string sql = $"select * from {tableName}";
                OdbcCommand odc = new OdbcCommand(sql, conn);
                odc.ExecuteNonQuery();
                odc.Dispose();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public static DataTable GetDataTable(string connStr, string tableName)
        {
            if (!IsTableExist(connStr, tableName))
            {
                return null;
            }

            using OdbcConnection conn = new OdbcConnection(connStr);
            try
            {
                conn.Open();
                string sql = $"select * from {tableName}";
                DataTable dt = new DataTable();
                OdbcDataAdapter oda = new OdbcDataAdapter(sql, conn);
                oda.Fill(dt);
                oda.Dispose();
                dt.TableName = tableName;
                return dt;
            }
            catch (Exception)
            {
                return null;
            }
        }

        #endregion


    }

}
